<?php

/**
 * 
 * @project     : innoTweets 1.0
 * @author      : Dino (DBK)
 * 
 * @package     : Group Model
 * @description : Database functions related to recreation groups
 * 
 */
class Group_model extends CI_Model {

  function __construct() {
    parent::__construct();
  }

  // Get groups of the given year
  function get_groups($year = NULL, $limit = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($year) && is_numeric($year)) {
      $query_cond = array();
      $query_cond['group_year'] = $year;
      $query_cond['group_status'] = 1;
      $this->db->order_by('group_score', 'desc');
      $query_exec = $this->db->get_where('groups', $query_cond, $limit);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->result_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get all group years
  function get_group_years() {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    $query_sql = "SELECT DISTINCT group_year AS group_years FROM groups WHERE group_status = 1";
    $query_exec = $this->db->query($query_sql);
    if ($query_exec == TRUE) {
      $result_data = array();
      $result_data['status'] = 1;
      $result_data['result'] = $query_exec->result_array();
      $result_data['count'] = $query_exec->num_rows();
    }
    return $result_data;
  }

  // Get group members by group id
  function get_group_members($group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_id) && is_numeric($group_id)) {
      $query_cond = array();
      $query_cond['group_member_group_id'] = $group_id;
      $query_cond['user_status'] = 1;
      $this->db->join('users', 'user_id = group_member_user_id');
      $this->db->order_by('user_join_date');
      $query_exec = $this->db->get_where('groups_members', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->result_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get all group events of provided year
  function get_group_events($year = NULL, $limit = NULL, $sort_by = 'desc') {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($year) && is_numeric($year)) {
      $query_cond = array();
      $query_cond['group_event_year'] = $year;
      $query_cond['group_event_status'] = 1;
      $this->db->order_by('group_event_date', $sort_by);
      $query_exec = $this->db->get_where('groups_events', $query_cond, $limit);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->result_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get group event results by group event id
  function get_group_event_results($group_event_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_event_id) && is_numeric($group_event_id)) {
      $query_cond = array();
      $query_cond['group_event_result_event_id'] = $group_event_id;
      $this->db->join('groups', 'group_event_result_group_id = group_id');
      $this->db->order_by('group_event_result_score', 'desc');
      $query_exec = $this->db->get_where('groups_events_results', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->result_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get group event result by group event id and group id
  function get_group_event_result_by_group($group_event_id = NULL, $group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_event_id) && is_numeric($group_event_id) && !empty($group_id) && is_numeric($group_id)) {
      $query_cond = array();
      $query_cond['group_event_result_event_id'] = $group_event_id;
      $query_cond['group_event_result_group_id'] = $group_id;
      //$this->db->select('groups_events_results.*', FALSE);
      //$this->db->select('(SELECT SUM(group_event_result_score) FROM groups_events_results ger JOIN groups_events ge ON ge.group_event_id = ger.group_event_result_event_id WHERE ge.group_event_status = 1 AND group_event_result_event_id = ' . $group_event_id . ') AS total_event_score', FALSE);
      $query_exec = $this->db->get_where('groups_events_results', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->row_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Update group scores by calculating from event results
  function update_group_scores() {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    $query_sql = "UPDATE groups g SET g.group_score = (SELECT SUM(group_event_result_score) FROM groups_events_results ger JOIN groups_events ge ON ge.group_event_id = ger.group_event_result_event_id WHERE ge.group_event_status = 1 AND group_event_result_group_id = g.group_id)";
    $query_exec = $this->db->query($query_sql);
    if ($query_exec == TRUE) {
      $result_data = array();
      $result_data['status'] = 1;
      $result_data['result'] = $this->db->affected_rows();
      $result_data['count'] = 1;
    }
    return $result_data;
  }

  // Get groups of the given year
  function get_group_by_name($group_name = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_name)) {
      $query_cond = array();
      $query_cond['group_name'] = $group_name;
      $query_exec = $this->db->get_where('groups', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->row_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Create a new group for current year
  function set_group($group_name = NULL, $group_status = 1) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_name)) {
      $query_insr = array(
          'group_name' => $group_name,
          'group_year' => NOW_YEAR,
          'group_score' => 0,
          'group_c_user_id' => 0,
          'group_vc_user_id' => 0,
          'group_cm_user_id' => 0,
          'group_status' => $group_status
      );
      $query_exec = $this->db->insert('groups', $query_insr);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $this->db->insert_id();
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Update a group details by group id
  function update_group($group_id = NULL, $group_name = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_name) && !empty($group_id) && is_numeric($group_id)) {
      $query_upda = array('group_name' => $group_name);
      $query_cond = array('group_id' => $group_id);
      $query_exec = $this->db->update('groups', $query_upda, $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $this->db->affected_rows();
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Delete a group by group id
  function delete_group($group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_id) && is_numeric($group_id)) {
      $query_cond = array();
      $query_cond['group_id'] = $group_id;
      $query_exec = $this->db->delete('groups', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $this->db->affected_rows();
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Get users which are not added to any group
  function get_ungrouped_members($group_year = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_year)) {
      $query_sql = "SELECT * FROM users u WHERE u.user_status = 1 AND user_resigned = 0 AND u.user_id NOT IN (SELECT gm.group_member_user_id FROM groups_members gm JOIN groups g ON g.group_id = gm.group_member_group_id WHERE group_year = '{$group_year}')";
      $query_exec = $this->db->query($query_sql);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->result_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get users which are added to any group
  function get_grouped_members($group_year = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_year)) {
      $query_sql = "SELECT * FROM users u WHERE u.user_status = 1 AND u.user_id IN (SELECT gm.group_member_user_id FROM groups_members gm JOIN groups g ON g.group_id = gm.group_member_group_id WHERE group_year = '{$group_year}')";
      $query_exec = $this->db->query($query_sql);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->result_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get group details by group id
  function get_group_by_id($group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_id) && is_numeric($group_id)) {
      $query_cond = array();
      $query_cond['group_id'] = $group_id;
      $query_cond['group_status'] = 1;
      $query_exec = $this->db->get_where('groups', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->row_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Get group of user by group year
  function get_user_group_by_year($user_id = NULL, $group_year = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($user_id) && !empty($group_year)) {
      $query_cond = array();
      $query_cond['group_year'] = $group_year;
      $query_cond['group_member_user_id'] = $user_id;
      $this->db->join('groups', 'group_id = group_member_group_id');
      $query_exec = $this->db->get_where('groups_members', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->row_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Add a user to a group
  function set_group_member($group_id = NULL, $user_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_id) && is_numeric($group_id) && !empty($user_id) && is_numeric($user_id)) {
      $query_insr = array(
          'group_member_group_id' => $group_id,
          'group_member_user_id' => $user_id
      );
      $query_exec = $this->db->insert('groups_members', $query_insr);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Remove a user from a group
  function remove_group_member($user_id = NULL, $group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($user_id) && !empty($group_id)) {
      $query_cond = array();
      $query_cond['group_member_user_id'] = $user_id;
      $query_cond['group_member_group_id'] = $group_id;
      $query_exec = $this->db->delete('groups_members', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Remove all users in a group
  function remove_group_all_members($group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_id) && is_numeric($group_id)) {
      $query_cond = array();
      $query_cond['group_member_group_id'] = $group_id;
      $query_exec = $this->db->delete('groups_members', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Set group member titles
  function update_group_member($group_id = NULL, $capitan_user_id = NULL, $vice_capitan_user_id = NULL, $comitte_member_user_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    $query_upda = array();
    if (!empty($capitan_user_id) && is_numeric($capitan_user_id)) {
      $query_upda['group_c_user_id'] = $capitan_user_id;
    } else {
      $query_upda['group_c_user_id'] = NULL;
    }
    if (!empty($vice_capitan_user_id) && is_numeric($vice_capitan_user_id)) {
      $query_upda['group_vc_user_id'] = $vice_capitan_user_id;
    } else {
      $query_upda['group_vc_user_id'] = NULL;
    }
    if (!empty($comitte_member_user_id) && is_numeric($comitte_member_user_id)) {
      $query_upda['group_cm_user_id'] = $comitte_member_user_id;
    } else {
      $query_upda['group_cm_user_id'] = NULL;
    }
    $query_cond = array('group_id' => $group_id);
    $query_exec = $this->db->update('groups', $query_upda, $query_cond);
    if ($query_exec == TRUE) {
      $result_data = array();
      $result_data['status'] = 1;
      $result_data['result'] = $this->db->affected_rows();
      $result_data['count'] = 1;
    }
    return $result_data;
  }

  // Create a new group event
  function set_group_event($group_event_name = NULL, $group_event_date = NULL, $group_event_max_score = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_event_name) && !empty($group_event_date) && !empty($group_event_max_score)) {
      $query_insr = array(
          'group_event_name' => $group_event_name,
          'group_event_max_score' => $group_event_max_score,
          'group_event_date' => date("Y-m-d H:i:s", strtotime($group_event_date)),
          'group_event_year' => NOW_YEAR,
          'group_event_status' => 1
      );
      $query_exec = $this->db->insert('groups_events', $query_insr);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Delete a group event by group event id
  function delete_group_event($group_event_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_event_id) && is_numeric($group_event_id)) {
      $query_upda = array('group_event_status' => 0);
      $query_cond = array('group_event_id' => $group_event_id);
      $query_exec = $this->db->update('groups_events', $query_upda, $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $this->db->affected_rows();
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Add results to group event
  function set_group_event_result($group_event_id = NULL, $group_id = NULL, $group_score = 0, $add_info = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (empty($group_score) && !is_numeric($group_score)) {
      $group_score = 0;
    }
    if (!empty($group_event_id) && is_numeric($group_event_id) && !empty($group_id)) {
      $query_insr = array(
          'group_event_result_event_id' => $group_event_id,
          'group_event_result_group_id' => $group_id,
          'group_event_result_score' => $group_score,
          'group_event_result_info' => $add_info
      );
      $query_exec = $this->db->insert('groups_events_results', $query_insr);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Get group event by group event id
  function get_group_event_by_id($group_event_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_event_id) && is_numeric($group_event_id)) {
      $query_cond = array();
      $query_cond['group_event_id'] = $group_event_id;
      $query_cond['group_event_status'] = 1;
      $query_exec = $this->db->get_where('groups_events', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = $query_exec->row_array();
        $result_data['count'] = $query_exec->num_rows();
      }
    }
    return $result_data;
  }

  // Update group event result by group event id
  function update_group_event_result($group_event_id = NULL, $group_id = NULL, $group_score = 0, $add_info = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (empty($group_score) && !is_numeric($group_score)) {
      $group_score = 0;
    }
    if (!empty($group_event_id) && is_numeric($group_event_id) && !empty($group_id)) {
      $query_upda = array();
      $query_upda['group_event_result_score'] = $group_score;
      $query_upda['group_event_result_info'] = $add_info;
      $query_cond = array();
      $query_cond['group_event_result_event_id'] = $group_event_id;
      $query_cond['group_event_result_group_id'] = $group_id;
      $query_exec = $this->db->update('groups_events_results', $query_upda, $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

  // Remove all users in a group
  function remove_group_all_results($group_id = NULL) {
    $result_data = array();
    $result_data['status'] = 0;
    $result_data['result'] = '';
    $result_data['count'] = 0;
    if (!empty($group_id) && is_numeric($group_id)) {
      $query_cond = array();
      $query_cond['group_event_result_group_id'] = $group_id;
      $query_exec = $this->db->delete('groups_events_results', $query_cond);
      if ($query_exec == TRUE) {
        $result_data = array();
        $result_data['status'] = 1;
        $result_data['result'] = 1;
        $result_data['count'] = 1;
      }
    }
    return $result_data;
  }

}

?>
